package com.lobaby.tools.modules.excel.service.impl;

import com.lobaby.tools.common.properties.SystemParamKey;
import com.lobaby.tools.common.utils.DateUtils;
import com.lobaby.tools.common.utils.ExcelUtils;
import com.lobaby.tools.common.utils.StringUtil;
import com.lobaby.tools.model.ConsoleResult;
import com.lobaby.tools.model.CountObj;
import com.lobaby.tools.model.Employee;
import com.lobaby.tools.model.OutputData;
import com.lobaby.tools.modules.excel.service.IExcelService;
import com.lobaby.tools.modules.pdf.service.impl.PdfServiceImpl;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.Collator;
import java.util.*;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @author Administrator
 *         Created by Administrator on 2021/3/1 0001.
 */
@Service
public class ExcelServiceImpl implements IExcelService {
    @Autowired
    private SystemParamKey prop;

    @Override
    public ConsoleResult lateCount(MultipartFile file) throws Exception {
        if (file != null) {
            File resoureDir = StringUtil.getCurrentJarPathFile(PdfServiceImpl.class, "/resource/excel/");

            String filePath = resoureDir.getAbsolutePath() + "\\打卡异常记录.xlsx";
            return processLateCount2020(file.getInputStream(), StringUtil.getFileType(file.getOriginalFilename()), filePath);
        } else {
            return ConsoleResult.getErrObj("获取文件失败，请稍后重试！");
        }
    }

    private ConsoleResult processLateCount2020(InputStream is, String fileType, String targetPath) throws Exception {
        try {
            FileOutputStream os = new FileOutputStream(new File(targetPath));

            XSSFWorkbook input = new XSSFWorkbook(is);
            XSSFSheet inputSheet = input.getSheetAt(0);
            int startRow = 1;
            int lastRowNum = inputSheet.getLastRowNum();
            Map<String, List<CountObj>> result = new LinkedHashMap<>();
            for (int i = startRow; i < lastRowNum+1; i++) {
                XSSFRow operRow = inputSheet.getRow(i);
                if (operRow != null) {
                    // 有迟到早退
                    String name = operRow.getCell(2).getStringCellValue();
                    String deptName = operRow.getCell(3).getStringCellValue();
                    String key = name+":"+deptName;
                    if (result.get(key) != null) {
                        // 不为空，添加元素
                        List<CountObj> data = result.get(key);
                        CountObj item = new CountObj(operRow);
                        data.add(item);
                    } else {
                        // 为空，添加 List
                        List<CountObj> data = new LinkedList<>();
                        CountObj item = new CountObj(operRow);
                        data.add(item);
                        result.put(key, data);
                    }
                }
            }

            Map<String, List<String>> insertData = new LinkedHashMap<>();

            int sn = 1;
            String regEx="[^0-9]";
            Pattern p = Pattern.compile(regEx);
            for (String key : result.keySet()) {
                List<CountObj> datas = result.get(key);

                OutputData count = new OutputData();
                for (int i = 0, n = datas.size(); i < n; i++) {
                    CountObj countObj = datas.get(i);

                    String startStatus = countObj.getStartStatus();
                    if (startStatus.indexOf("范围外") != -1) {
                        startStatus = startStatus.substring(0, startStatus.indexOf("范围外"));
                    }
                    if (!StringUtil.isBlank(startStatus)) {
                        if (startStatus.indexOf("迟到") != -1) {
                            int lateMin = Integer.parseInt(p.matcher(startStatus).replaceAll("").trim());
                            if (lateMin > 10) {
                                String startTime = countObj.getStartTime();
                                String endTime = countObj.getEndTime();

                                long lessHour = 0;
                                if (countObj.getEndStatus().equals("正常")) {
                                    lessHour = DateUtils.getLessHour(startTime, endTime);
                                }

                                if (lessHour >= 9) {
                                    if (count.getMakenocount() != null) {
                                        count.setMakenocount(count.getMakenocount() + 1);
                                        count.setDxShow(count.getDxShow() + "\r\n" + "日期：" + countObj.getDate() + "，上班打卡时间：" + countObj.getStartTime() + ", 下班打卡时间：" + countObj.getEndTime());
                                    } else {
                                        count.setMakenocount(1);
                                        count.setDxShow("日期：" + countObj.getDate() + "，上班打卡时间：" + countObj.getStartTime() + ", 下班打卡时间：" + countObj.getEndTime());
                                    }
                                } else {
                                    if (count.getLatemin() != null) {
                                        count.setLatecount(count.getLatecount()+1);
                                        count.setLatemin(count.getLatemin() + Integer.parseInt(p.matcher(startStatus).replaceAll("").trim()));
                                        count.setLateShow(count.getLateShow() + "\r\n" + "日期：" + countObj.getDate() + "，上班打卡时间：" + countObj.getStartTime());
                                    } else {
                                        count.setLatecount(1);
                                        count.setLatemin(Integer.parseInt(p.matcher(startStatus).replaceAll("").trim()));
                                        count.setLateShow("日期：" + countObj.getDate() + "，上班打卡时间：" + countObj.getStartTime());
                                    }
                                }

                            }
                        } else if (startStatus.equals("未打卡")) {
                            if (count.getStartNoata() != null) {
                                count.setStartNoata(count.getStartNoata() + 1);
                                String currContent = count.getStartNoShow() + "\r\n 日期：" + countObj.getDate() + "，上班打卡状态：" + countObj.getStartStatus();
                                if (!StringUtil.isBlank(countObj.getRemark())) {
                                    currContent += "，备注：" + countObj.getRemark();
                                }
                                count.setStartNoShow(currContent);
                            } else {
                                count.setStartNoata(1);
                                String currContent = "日期：" + countObj.getDate() + "，上班打卡状态：" + countObj.getStartStatus();
                                if (!StringUtil.isBlank(countObj.getRemark())) {
                                    currContent += "，备注：" + countObj.getRemark();
                                }
                                count.setStartNoShow(currContent);
                            }
                        } else if (startStatus.equals("正常") && countObj.getEndStatus().contains("早退")) {
                            long lessHour = 0;
                            String startTime = countObj.getStartTime();
                            String endTime = countObj.getEndTime();
                            lessHour = DateUtils.getLessMinutes(startTime, endTime);

                            if (lessHour >= 540) {
                                if (count.getMakenocount() != null) {
                                    count.setMakenocount(count.getMakenocount() + 1);
                                    count.setDxShow(count.getDxShow() + "\r\n" + "日期：" + countObj.getDate() + "，上班打卡时间：" + countObj.getStartTime() + ", 下班打卡时间：" + countObj.getEndTime());
                                } else {
                                    count.setMakenocount(1);
                                    count.setDxShow("日期：" + countObj.getDate() + "，上班打卡时间：" + countObj.getStartTime() + ", 下班打卡时间：" + countObj.getEndTime());
                                }
                            }
                        }
                    }

                    String endStatus = countObj.getEndStatus();
                    if (!StringUtil.isBlank(endStatus)) {
                        if (endStatus.indexOf("早退") != -1) {
                            if (count.getZtcount() != null) {
                                count.setZtcount(count.getZtcount()+1);
                                String currContent = count.getZtShow() + "\r\n" + "日期：" + countObj.getDate() + "，下班打卡状态：" + countObj.getEndStatus();
                                if (!StringUtil.isBlank(countObj.getRemark())) {
                                    currContent += "，备注：" + countObj.getRemark();
                                }
                                count.setZtShow(currContent);
                            } else {
                                count.setZtcount(1);
                                String currContent = "日期：" + countObj.getDate() + "，下班打卡状态：" + countObj.getEndStatus();
                                if (!StringUtil.isBlank(countObj.getRemark())) {
                                    currContent += "，备注：" + countObj.getRemark();
                                }
                                count.setZtShow(currContent);
                            }
                        } else if (endStatus.equals("未打卡")) {
                            if (count.getEndNoata() != null) {
                                count.setEndNoata(count.getEndNoata() + 1);
                                String currContent = count.getEndNoShow() + "\r\n 日期：" + countObj.getDate() + "，下班打卡状态：" + countObj.getEndStatus();
                                if (!StringUtil.isBlank(countObj.getRemark())) {
                                    currContent += "，备注：" + countObj.getRemark();
                                }
                                count.setEndNoShow(currContent);
                            } else {
                                count.setEndNoata(1);
                                String currContent = "日期：" + countObj.getDate() + "，下班打卡状态：" + countObj.getEndStatus();
                                if (!StringUtil.isBlank(countObj.getRemark())) {
                                    currContent += "，备注：" + countObj.getRemark();
                                }
                                count.setEndNoShow(currContent);
                            }
                        } else if (endStatus.equals("正常")) {
                            String endTime = countObj.getEndTime();
                            String jbTime = "18:30";
                            long jbMinutes = DateUtils.getLessMinutes(jbTime, endTime);
                            if (jbMinutes >= 0) {
                                String startStatus1 = countObj.getStartStatus();
                                if (startStatus1.equals("正常")) {
                                    if (count.getJbmin() != null) {
                                        count.setJbmin(count.getJbmin() + Integer.parseInt(String.valueOf(jbMinutes)));
                                        count.setJbShow(count.getJbShow() + "\r\n" + "日期：" + countObj.getDate() + "，上班打卡时间：" + countObj.getStartTime() + ", 下班打卡时间：" + countObj.getEndTime());
                                    } else {
                                        count.setJbmin(Integer.parseInt(String.valueOf(jbMinutes)));
                                        count.setJbShow("日期：" + countObj.getDate() + "，上班打卡时间：" + countObj.getStartTime() + ", 下班打卡时间：" + countObj.getEndTime());
                                    }
                                } else if (startStatus1.indexOf("迟到") != -1) {
                                    int latemin = Integer.parseInt(p.matcher(startStatus).replaceAll("").trim());
                                    jbMinutes = jbMinutes - latemin;
                                    if (jbMinutes > 0) {
                                        if (count.getJbmin() != null) {
                                            count.setJbmin(count.getJbmin() + Integer.parseInt(String.valueOf(jbMinutes)));
                                            count.setJbShow(count.getJbShow() + "\r\n" + "日期：" + countObj.getDate() + "，上班打卡时间：" + countObj.getStartTime() + ", 下班打卡时间：" + countObj.getEndTime());
                                        } else {
                                            count.setJbmin(Integer.parseInt(String.valueOf(jbMinutes)));
                                            count.setJbShow("日期：" + countObj.getDate() + "，上班打卡时间：" + countObj.getStartTime() + ", 下班打卡时间：" + countObj.getEndTime());
                                        }
                                    }
                                }

                            }
                        }
                    }

                }

                List<String> rowData = new LinkedList<>();
                rowData.add(sn+"");
                rowData.add(datas.get(0).getDeptName().replace("北京能高自动化技术股份有限公司/", ""));
                rowData.add(datas.get(0).getName());
                rowData.add(count.getLatemin() == null ? "" : count.getLatemin()+"");
                rowData.add(count.getLatecount() == null ? "" : count.getLatecount()+"");
                rowData.add(count.getLateShow());
                rowData.add(count.getStartNoata() == null ? "" : count.getStartNoata()+"");
                rowData.add(count.getStartNoShow());
                rowData.add(count.getZtcount() == null ? "" : count.getZtcount()+"");
                rowData.add(count.getZtShow());
                rowData.add(count.getEndNoata() == null ? "" : count.getEndNoata()+"");
                rowData.add(count.getEndNoShow());
                rowData.add(count.getJbmin() == null ? "" : count.getJbmin()+"");
                rowData.add(count.getJbShow());
                rowData.add(count.getMakenocount() == null ? "" : count.getMakenocount()+"");
                rowData.add(count.getDxShow());
                insertData.put(key, rowData);

                sn++;
            }
            XSSFWorkbook output = new XSSFWorkbook();

            XSSFCellStyle cellStyle = output.createCellStyle();
            cellStyle.setWrapText(true);

            XSSFSheet outSheet = output.createSheet();
            XSSFRow titleRow = outSheet.createRow(0);

            String[] titleContent = prop.getTitleContent().split(",");

            for (int i = 0; i < titleContent.length; i++) {
                XSSFCell cell = titleRow.createCell(i);
                cell.setCellValue(titleContent[i]);
            }

            int row = 1;
            for (String key : insertData.keySet()) {
                List<String> list = insertData.get(key);
                XSSFRow operRow = outSheet.createRow(row);
                for (int j = 0, n = list.size(); j < n; j++) {
                    XSSFCell cell = operRow.createCell(j);
                    outSheet.autoSizeColumn(j);
                    outSheet.setColumnWidth(j, outSheet.getColumnWidth(j) * 17 / 10);
                    cell.setCellValue(list.get(j));
                    cell.setCellType(CellType.STRING);
                }

                row++;
            }

            output.write(os);

            input.close();
            output.close();

            is.close();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        ConsoleResult result = ConsoleResult.getSuccessObj("成功！");
        result.setObject(targetPath);
        return result;
    }

    @Deprecated
    private ConsoleResult processLateCount(InputStream is, String fileType, String targetPath) throws Exception {
        // 源文件
        Workbook workbook = ExcelUtils.getWorkbook(is, fileType);

        Sheet sheet = ExcelUtils.getSheet(workbook, "上下班打卡_日报");
        if (sheet == null) {
            return ConsoleResult.getErrObj("Excel文件错误，请选择打卡记录对应的Excel文档！");
        } else {
            Map<String, List<Employee>> data = new HashMap<>();
            // 获取标题行
            row:for (int i = 4, n = sheet.getLastRowNum(); i < n; i++) {
                Row title = sheet.getRow(i);

                // 星期
                String week = title.getCell(1).getStringCellValue();
                // 上班打卡状态
                String first = title.getCell(39).getStringCellValue();
                // 下班打卡状态
                String end = title.getCell(41).getStringCellValue();
                if (week.equals("星期六") || week.equals("星期日")) {
                    // 周末不统计
                    continue row;
                } else {
                    if (first.equals("正常") && end.equals("正常")) {
                        // 都是正常的不统计
                        continue row;
                    } else {
                        if (end.equals("正常") && StringUtil.parseInt(first) <= 10) {
                            // 迟到10分钟以内不统计
                            continue row;
                        } else {
                            String deptDesc = title.getCell(4).getStringCellValue();
                            if (deptDesc.indexOf("青海") > 0 || deptDesc.indexOf("集团") > 0 || deptDesc.indexOf("敦煌能高") > 0) {
                                // 不统计青海、集团、敦煌能高的
                                continue row;
                            } else {
                                // 异常的，需要统计
                                String[] split = deptDesc.split("/");
                                String deptName = split[split.length - 1];
                                deptName = getDeptName(deptName);
                                if (data.get(deptName) == null) {
                                    Employee employee = new Employee(title);
                                    List<Employee> employees = new ArrayList<>();
                                    employees.add(employee);
                                    data.put(deptName, employees);
                                } else {
                                    Employee employee = new Employee(title);
                                    data.get(deptName).add(employee);
                                }
                            }

                        }
                    }
                }
            }

            String[] titles = {"日期", "星期", "姓名", "部门", "上班打卡时间", "上班打卡状态", "下班打卡时间", "下班打卡状态", "备注"};
            String[] props = {"date","week","name","deptname","starttime","startstatus","endtime","endstatus","remark"};
            // 目标文件
            XSSFWorkbook target = new XSSFWorkbook();
            for (String key : data.keySet()) {
                List<Employee> employees = data.get(key);
                employees = employees.stream().sorted((x, y) -> {
                    Comparator cmp = Collator.getInstance(java.util.Locale.CHINA);
                    int compare = cmp.compare(x.getName(), y.getName());
                    if (compare == 0) {
                        compare = x.getDate().compareTo(y.getDate());
                    }

                    return compare;
                }).collect(Collectors.toList());

                XSSFSheet deptSheet = target.createSheet(key);
                // 设置自动列宽，根据标题设置列的宽度
//                Stream.iterate(0, i -> i + 1).limit(titles.length).forEach(x -> deptSheet.autoSizeColumn(x));

                XSSFRow titleRow = deptSheet.createRow(0);
                for (int i = 0; i < titles.length; i++) {
                    titleRow.createCell(i).setCellValue(titles[i]);
                }
                for (int i = 1, n = employees.size(); i <= n; i++) {
                    XSSFRow row = deptSheet.createRow(i);
                    Employee obj = employees.get(i - 1);
                    for (int j = 0; j < props.length; j++) {
                        Class<? extends Object> clazz = obj.getClass();

                        Field field = clazz.getDeclaredField(props[j]);
                        field.setAccessible(true);
                        String value = field.get(obj) == null ? "" : field.get(obj).toString();

                        XSSFCell cell = row.createCell(j);
                        cell.setCellValue(value);
                        if (props[j].equals("startstatus") || "endstatus".equals(props[j])) {
                            if (!"正常".equals(value)) {
                                cell.setCellStyle(ExcelUtils.getStyle(target, "colorError"));
                                deptSheet.setColumnWidth(j, value.getBytes().length * 256);
                            }
                        } else if (props[j].equals("name")) {
                            deptSheet.setColumnWidth(j, value.getBytes().length * 2 * 256);
                        } else {
                            if (value.substring(0, 1).matches("[0-9]")) {
                                deptSheet.setColumnWidth(j, value.getBytes().length * 2 * 256);
                            } else {
                                deptSheet.setColumnWidth(j, value.getBytes().length * 256);
                            }
                            cell.setCellStyle(ExcelUtils.getStyle(target, "autoFiexd"));
                        }

                    }
                }
            }
            File targetFile = new File(targetPath);
            target.write(new FileOutputStream(targetFile));
            ConsoleResult result = ConsoleResult.getSuccessObj("成功！");
            result.setObject(targetPath);
            return result;
        }
    }

    private String getDeptName(String name) {
        if (name.equals("项目管理部") || name.equals("品控部")) {
            return "企管中心";
        } else if (name.equals("系统技术部") || name.equals("工程部") || name.equals("综合部")) {
            return "工程中心";
        } else {
            return name;
        }
    }

}